"""
飞检数据STEP8筛选 - output模块
============================================================================

将 VLT2 的数据输出到指定的文件夹中

主要功能：
1. 在指定目录下创建 example/{hspAbbr} 命名的子目录
2. 查询 VLT2 表中医院与 RULE_ID 的去重，制作任务清单
3. 查询 RULE_FLATTEN，KEY like description，查 rule_id 和 value，制作成键是ruleId值是description字典
4. 逐条处理任务清单
    4.1. 查询该 hspAbbr 与 ruleId 下的 setl_id，关联 m 表，按 j_rand 排序，取前 5 条
    4.2. 查询这 5 条数据的主单信息
    4.3. 查询这 5 条数据的明细信息，其中在 vlt 当中的同一个 d0_rowid 的那些信息则在一个违规字段中标明 违规
    4.4. 将上述 5 个主单信息、5 个明细信息 输出 EXCEL 文件，sheet名分别是 病例1主单、病例1明细、病例2主单...，文件名是 {hspAbbr}_{ruleId}_{description}.xlsx，输出到目录
============================================================================
"""


import pandas as pd
import openpyxl
import sys
import os
import re

sys.path.append(os.path.abspath(os.path.join(os.path.dirname(__file__), '..')))
from config import create_db_engine

# 创建数据库引擎
engine = create_db_engine()
from sqlalchemy import text

import time


# 时间函数
t0 = time.time()
def elapsed() -> str:
    """返回自脚本启动以来的耗时 (HH:MM:SS)。
    用于将原先的 [INFO]/[START]/[DONE] 等等级标签替换成实时耗时。
    """
    
    # 实际时间，yyy-mm-dd hh:mm:ss
    timeStr = time.strftime("%H:%M:%S", time.localtime())
    
    # 已消耗时间 XX hours XX minutes XX.XX seconds
    delta = int(time.time() - t0)
    if delta < 60:
        return f"{timeStr} (+ {delta} sec)"
    elif delta < 3600:
        m, s = divmod(delta, 60)
        return f"{timeStr} (+ {m} min {s} sec)"
    elif delta < 86400:
        h, rem = divmod(delta, 3600)
        m, s = divmod(rem, 60)
        return f"{timeStr} (+ {h} hour {m} min {s} sec)"
    else:
        d, rem = divmod(delta, 86400)
        h, rem = divmod(rem, 3600)
        m, s = divmod(rem, 60)
        return f"{timeStr} (+ {d} day {h} hour {m} min {s} sec)"

# ============ 
# step1: 查询任务清单
# ============
def fetch_task_list(engine):
    query = """
    SELECT DISTINCT hsp_abbr, rule_id
    FROM vlt2
    ORDER BY hsp_abbr, rule_id
    """
    taskDf = pd.read_sql(query, engine)
    # 转换为列表
    taskList = taskDf.to_dict(orient='records')
    print(f"[{elapsed()}] Fetched {len(taskList)} tasks")
    return taskList


# ============ 
# step2: 创建输出目录
# ============
def create_output_dirs(base_dir, hsp_abbrs):
    for hsp_abbr in hsp_abbrs:
        dir_path = os.path.join(base_dir, 'example', hsp_abbr)
        os.makedirs(dir_path, exist_ok=True)
        print(f"[{elapsed()}] Created directory: {dir_path}")


# ============ 
# step3: 制作规则描述 ID
# ============
def fetch_rule_descriptions(engine):
    query = f"""
    SELECT rule_id, value AS description
    FROM rule_flatten
    WHERE key like '%description%'
    """
    ruleDf = pd.read_sql(query, engine)
    ruleDict = pd.Series(ruleDf.description.values, index=ruleDf.rule_id).to_dict()
    print(f"[{elapsed()}] Fetched {len(ruleDict)} rule descriptions")
    return ruleDict


ILLEGAL_CHAR_PATTERN = re.compile(r"[\x00-\x08\x0B\x0C\x0E-\x1F\x7F]")


def _sanitize_dataframe(df: pd.DataFrame) -> pd.DataFrame:
    """Strip characters Excel/openpyxl forbids so worksheet writes do not fail."""
    if df is None or df.empty:
        return df

    object_cols = df.select_dtypes(include=["object"]).columns
    for col in object_cols:
        df[col] = df[col].map(
            lambda val: ILLEGAL_CHAR_PATTERN.sub("", val) if isinstance(val, str) else val
        )
    return df

# ============ 
# step4: 逐条处理任务清单，输出 EXCEL 文件
# ============
def _get_example_setl_ids(engine, hsp_abbr, rule_id):
    sql = text(f"""
        SELECT m.setl_id
        FROM m
        WHERE EXISTS (
            SELECT 1 FROM vlt2 WHERE hsp_abbr = :hsp_abbr AND rule_id = :rule_id AND vlt2.setl_id = m.setl_id
        )
        ORDER BY m.j_rand, m.setl_id
        FETCH FIRST 5 ROWS ONLY
    """)
    df = pd.read_sql(sql, engine, params={'hsp_abbr': hsp_abbr, 'rule_id': rule_id})
    return df['setl_id'].tolist()

def _get_setl_main(engine, setl_id, rule_id, description):
    sql = text(f"""
        WITH VLT_AGG AS (
            SELECT 
                setl_id,
                MAX(dxmaincode) AS dxmaincode,
                MAX(dxmainname) AS dxmainname,
                MAX(dxcodeall) AS dxcodeall,
                MAX(dxnameall) AS dxnameall,
                MAX(txmaincode) AS txmaincode,
                MAX(txmainname) AS txmainname,
                MAX(txcodeall) AS txcodeall,
                MAX(txnameall) AS txnameall,
                MAX(rule_id) AS rule_id,
                MAX(rule_act_id) AS rule_act_id,
                MAX(rule_act_msg) AS rule_act_msg
            FROM vlt2
            WHERE setl_id = :setl_id AND rule_id = :rule_id
            GROUP BY setl_id
        )
        SELECT 
          vs.结算ID,
          vs.人员编号,
          vs.人员姓名,
          vs.人员证件类型,
          vs.证件号码,
          vs.性别,
          vs.出生日期,
          vs.年龄,
          vs.险种类型,
          vs.人员类别,
          vs.定点医药机构编号,
          vs.定点医药机构名称,
          vs.开始日期,
          vs.结束日期,
          vs.结算时间,
          vs.医疗类别,
          vmd."住院/门诊号",
          vmd.入院科室名称,
          vmd.出院科室名称,
          vmd.离院方式,
          vmd.病种编号,
          vmd.病种名称,
          vmd.门诊诊断信息,
          vmd.入院科室名称,
          vmd.出院科室名称,
          vlt_agg.dxmaincode   AS 主要诊断代码,
          vlt_agg.dxmainname   AS 主要诊断名称,
          vlt_agg.dxcodeall    AS 全诊断编码,
          vlt_agg.dxnameall    AS 全诊断名称,
          vlt_agg.txmaincode   AS 主要手术操作代码,
          vlt_agg.txmainname   AS 主要手术操作名称,
          vlt_agg.txcodeall    AS 全手术操作编码,
          vlt_agg.txnameall    AS 全手术操作名称,
          vlt_agg.rule_id      AS 规则ID,
          vlt_agg.rule_act_id  AS 规则动作ID,
          vlt_agg.rule_act_msg AS 规则动作信息,
          :description         AS 规则描述
        FROM v_setl_map vs
        JOIN v_mdtrt_map vmd ON vs.就诊ID = vmd.就诊ID
        LEFT JOIN VLT_AGG vlt_agg ON vlt_agg.setl_id = vs.结算ID
        WHERE vs.结算ID = :setl_id
    """)
    df = pd.read_sql(sql, engine, params={'setl_id': setl_id, 'rule_id': rule_id, 'description': description})
    # df 将第一行转置，键为 "字段名"，值为 "字段值"
    df = df.T
    df.columns = ['值']
    df.index.name = '字段名'
    df.reset_index(inplace=True)
    return df

def _get_setl_details(engine, setl_id, rule_id):
    sql = text(f"""
        WITH VLT_MARK AS (
            SELECT 
                setl_id,
                d0_rowid,
                MAX(item_code2) AS item_code2,
                MAX(item_name2) AS item_name2,
                MAX(q2)         AS q2,
                MAX(vlt_p)      AS vlt_p,
                MAX(vlt_q)      AS vlt_q,
                MAX(vlt_c)      AS vlt_c,
                MAX(vlt_b)      AS vlt_b,
                MAX(related)    AS related,
                MAX(rule_act_msg) AS rule_act_msg
            FROM vlt
            WHERE setl_id = :setl_id AND rule_id = :rule_id
            GROUP BY setl_id, d0_rowid
        )
        SELECT
            d.d0_rowid,
            to_char(d.item_day, 'yyyy-mm-dd') AS item_day,
            to_char(d.item_time, 'hh24:mi:ss') AS item_time,
            d.item_dept,
            d.apply_dept,
            d.item_code,
            d.item_name,
            d.item_med_code,
            d.item_hsp_code,
            d.item_hsp_name,
            d.p,
            d.q,
            d.c,
            d.b,
            vm.item_code2,
            vm.item_name2,
            vm.q2,
            vm.related,
            vm.vlt_p,
            vm.vlt_q,
            vm.vlt_c,
            vm.vlt_b,
            vm.rule_act_msg
        FROM d 
        LEFT JOIN VLT_MARK vm 
          ON vm.setl_id = d.setl_id AND vm.d0_rowid = d.d0_rowid
        WHERE d.setl_id = :setl_id
        ORDER BY d.item_time_seq
    """)
    df = pd.read_sql(sql, engine, params={'setl_id': setl_id, 'rule_id': rule_id})
    return df

def _decorate_details(filePath):
    # 多个底色
    colors = ["FFCCCC", "CCFFCC", "CCCCFF", "FFFFCC", "CCFFFF", "FFCCFF"]    

    # 用 openpyxl 打开文件
    wb = openpyxl.load_workbook(filePath)
    # 处理第 1，3，5，7，9 个sheet（如有那么多）
    for i in range(1, 11, 2):
        sheet_name = f"病例{(i//2)+1}主单"
        if sheet_name in wb.sheetnames:
            ws = wb[sheet_name]
            # 第一列列宽加长到20，向右对齐
            ws.column_dimensions['A'].width = 20
            for r in range(1, ws.max_row + 1):
                ws.cell(row=r, column=1).alignment = openpyxl.styles.Alignment(horizontal='right')
            # 第二列列宽加长到30，向左对齐
            ws.column_dimensions['B'].width = 30
            for r in range(1, ws.max_row + 1):
                ws.cell(row=r, column=2).alignment = openpyxl.styles.Alignment(horizontal='left')
    # 处理第 2，4，6，8，10 个sheet（如有那么多）
    for i in range(2, 12, 2):
        sheet_name = f"病例{(i//2)}明细"
        if sheet_name in wb.sheetnames:
            ws = wb[sheet_name]
            # 组装表头映射（小写 -> 列索引）
            header_map = {}
            for col in range(1, ws.max_column + 1):
                name = ws.cell(row=1, column=col).value
                if name is None:
                    continue
                header_map[str(name).strip().lower()] = col

            # 查找违规标记列与日期列
            vlt_c_colindex = header_map.get('vlt_c') or header_map.get('vltc') or header_map.get('违规') or header_map.get('违规标记')
            rule_msg_colindex = header_map.get('rule_act_msg') or header_map.get('规则动作信息')
            day_colindex = header_map.get('item_day') or header_map.get('日期') or 2

            # 每一行当存在违规标记（优先 vlt_c 其次 rule_act_msg）时，将整行文字加粗为红色，下划线
            if vlt_c_colindex or rule_msg_colindex:
                for r in range(2, ws.max_row + 1):
                    flag_val = None
                    if vlt_c_colindex:
                        flag_val = ws.cell(row=r, column=vlt_c_colindex).value
                    elif rule_msg_colindex:
                        flag_val = ws.cell(row=r, column=rule_msg_colindex).value
                    if flag_val not in (None, ""):
                        for c in range(1, ws.max_column + 1):
                            ws.cell(row=r, column=c).font = openpyxl.styles.Font(bold=True, color="FF0000", underline="single")
            
            # E,F,G,H,I 列宽加长到15
            for col in ['B', 'C', 'E', 'F', 'G', 'H', 'I']:
                ws.column_dimensions[col].width = 15

            # 当第2列 item_day 与上一行不同时时，整行填充不同底色
            last_day = None
            color_index = -1
            max_col = ws.max_column
            for r in range(2, ws.max_row + 1):
                day_cell = ws.cell(row=r, column=day_colindex)
                day_val = day_cell.value
                if day_val != last_day:
                    last_day = day_val
                    color_index = (color_index + 1) % len(colors)
                fill = openpyxl.styles.PatternFill(start_color=colors[color_index], end_color=colors[color_index], fill_type="solid")
                for c in range(1, max_col + 1):
                    ws.cell(row=r, column=c).fill = fill
    # 保存文件
    wb.save(filePath)

def _output_single_task(engine, task, ruleDict, output_file):
    hsp_abbr = task['hsp_abbr']
    rule_id = task['rule_id']
    description = ruleDict.get(rule_id, '未知规则')
    # 获取示例结算ID
    setl_ids = _get_example_setl_ids(engine, hsp_abbr, rule_id)
    if not setl_ids:
        print(f"[{elapsed()}] 警告：{hsp_abbr}-{rule_id} 未找到样本，跳过输出。")
        return
    # 获取主单和明细
    mainSheets = []
    detailSheets = []
    for idx, setl_id in enumerate(setl_ids):
        mainDf = _get_setl_main(engine, setl_id, rule_id, description)
        detailDf = _get_setl_details(engine, setl_id, rule_id)
        mainSheets.append((f"病例{idx + 1}主单", mainDf))
        detailSheets.append((f"病例{idx + 1}明细", detailDf))
    
    # 输出到 Excel
    with pd.ExcelWriter(output_file, engine='openpyxl') as writer:
        for setl_id_ord, setl_id in enumerate(setl_ids):
            mainSheetName, mainDf = mainSheets[setl_id_ord]
            detailSheetName, detailDf = detailSheets[setl_id_ord]
            mainDf = _sanitize_dataframe(mainDf)
            detailDf = _sanitize_dataframe(detailDf)
            mainDf.to_excel(writer, sheet_name=mainSheetName, index=False)
            detailDf.to_excel(writer, sheet_name=detailSheetName, index=False)
    
    # 美化 Excel
    _decorate_details(output_file)


def output(engine, taskList, ruleDict, output_base_dir):
    total_tasks = len(taskList)
    for idx, task in enumerate(taskList):
        hsp_abbr = task['hsp_abbr']
        rule_id = task['rule_id']
        description = ruleDict.get(rule_id, '未知规则')
        safe_description = ''.join(c if c.isalnum() or c in (' ', '_') else '_' for c in description)
        output_file = os.path.join(output_base_dir, 'example', hsp_abbr, f"{hsp_abbr}_{rule_id}_example_{safe_description}.xlsx")
        
        print(f"[{elapsed()}] Processing task {idx + 1}/{total_tasks}: {hsp_abbr}, {rule_id} -> {output_file}")
        
        _output_single_task(engine, task, ruleDict, output_file)
        
        print(f"[{elapsed()}] Completed task {idx + 1}/{total_tasks}")

# ============ 
# 主流程
# ============
if __name__ == "__main__":
    base_dir = os.path.dirname(os.path.abspath(__file__))
    
    # Step 1: 查询任务清单
    taskList = fetch_task_list(engine)
    # 可选：通过环境变量 LIMIT_TASKS 只处理前 N 条，便于快速验证
    # _limit = 1
    # if _limit and _limit.isdigit():
    #     taskList = taskList[:int(_limit)]
    hspAbbrList = sorted(set(task['hsp_abbr'] for task in taskList))

    # Step 2: 创建输出目录
    create_output_dirs(base_dir, hspAbbrList)
    
    # Step 3: 制作规则描述 ID
    ruleDict = fetch_rule_descriptions(engine)
    
    # Step 4: 逐条处理任务清单，输出 EXCEL 文件
    output(engine, taskList, ruleDict, base_dir)
    
    print(f"[{elapsed()}] All tasks completed.")